--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

/*DROP TABLE se_pcp_repent;
CREATE TABLE se_pcp_repent AS (SELECT e.* FROM &erp.pcp_repent e WHERE 1=2);
DROP TABLE se_pcp_repdet;
CREATE TABLE se_pcp_repdet AS (SELECT d.* FROM &erp.pcp_repdet d WHERE 1=2);  */

DELETE FROM se_pcp_repent ;
DELETE FROM se_pcp_repdet ;


/*SELECT e.*,d.*
FROM   &erp.pcp_repent e, &erp.pcp_repdet d
WHERE e.rep_cod like 'AN%'
  AND d.parentenreg_id(+)=e.enreg_id */


 INSERT INTO se_pcp_repent
 (SELECT distinct
      'ARG_UNF' rep_cod,
      Trunc(SYSDATE,'YEAR') deb_dat,
      NULL fin_dat,
      null periode_val,
      null jour_dat,
      /*Nvl(Decode(p.codgestion,'5',conv.combsto_ref,p.numspecif+0),0)*/null combsto_id,
      NULL unfab_cod,
      NULL plan_cod,
      NULL cpte_cod,
      0 enreg_id,
      p.prodbase libre_val
  FROM uv_pcatalogues c, uv_pproduits p/*, temp_conv_rgp_combstoref conv*/
  WHERE c.REPARTPDP IS NOT null AND c.REPARTPDP !=0
    AND c.art=p.id(+)
    --AND c.art LIKE '23589999%'
    /*AND conv.clergp(+)=p.id*/ ) ;

 DELETE FROM se_pcp_repent WHERE rep_cod='ARG_UNF' AND libre_val IS NULL ;
 DELETE FROM se_pcp_repent r WHERE NOT EXISTS (SELECT 1 FROM &erp.art_gene g WHERE g.gene_cod||g.gene_ind= r.libre_val) ;

 UPDATE se_pcp_repent tf SET enreg_id=Nvl((select Max(enreg_id) FROM &erp.pcp_repent f WHERE f.rep_cod=tf.rep_cod AND f.libre_val =tf.libre_val AND f.deb_dat=tf.deb_dat),0) ;
 UPDATE se_pcp_repent tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0;


 INSERT INTO se_pcp_repdet
 (SELECT distinct
      Nvl((SELECT enreg_id FROM se_pcp_repent e
       WHERE e.rep_cod='ARG_UNF'
         AND e.libre_val=p.prodbase
         AND e.deb_dat=trunc(SYSDATE,'YEAR')
         ),0)   parentenreg_id,
      NULL periode_val,
      NULL jour_dat,
      NULL combsto_id,
      c.four unfab_cod,
      NULL cpte_cod,
      NULL plan_cod,
      0 enreg_id,
      NULL repp_val,
      c.REPARTPDP repc_val,
      NULL libre_val
  FROM uv_pcatalogues c, uv_pproduits p/*, temp_conv_rgp_combstoref conv*/
  WHERE c.REPARTPDP IS NOT NULL
       -- AND c.art LIKE '23589999%'
    AND c.art=p.id(+)

    /*AND conv.clergp(+)=p.id */) ;


 DELETE FROM se_pcp_repdet WHERE parentenreg_id=0 ;

 UPDATE se_pcp_repdet tf SET enreg_id=Nvl((select enreg_id FROM &erp.pcp_repdet f WHERE f.parentenreg_id=tf.parentenreg_id AND f.unfab_cod=tf.unfab_cod),0) ;
 UPDATE se_pcp_repdet tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id=0;

 CALL uvse_synchroverserp('rep_cod@deb_dat@periode_val@libre_val','PCP_REPENT','O') ;
 CALL uvse_synchroverserp('parentenreg_id@unfab_cod','PCP_REPDET','O') ;

 COMMIT ;




select * from (
SELECT &outils.stragg(source) source,rep_cod, deb_dat, fin_dat, periode_val, jour_dat, combsto_id, unfab_cod, plan_cod, cpte_cod, enreg_id, libre_val
FROM (
  SELECT 'uv' source,e.* FROM   se_pcp_repent e
  WHERE e.rep_cod='ARG_UNF'

  UNION ALL

  SELECT 'se' source,e.* FROM   &erp.pcp_repent e
  WHERE e.rep_cod='ARG_UNF'
  )
GROUP BY rep_cod, deb_dat, fin_dat, periode_val, jour_dat, combsto_id, unfab_cod, plan_cod, cpte_cod, enreg_id, libre_val
HAVING Count(1)!=2) r
ORDER BY combsto_id      ;



